package com.windy.spring.dao.jpa;

import java.text.DecimalFormat;
import java.util.List;

import javax.persistence.Query;
import javax.persistence.criteria.CriteriaQuery;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.windy.spring.dao.DaoPositionHistory;
import com.windy.spring.data.PositionHistory;

@Repository
public class JpaDaoPositionHistory extends JpaDao<PositionHistory, Integer> implements DaoPositionHistory {

	@Transactional
	public List<PositionHistory> findByFavUserGBuser(Integer favoriteUserId, Integer pageNumber, Integer rowsPerPage, String usernameFilter) {
		String queryString="" +
				"SELECT ph.* " +
				"FROM user u, position_history ph, spot s " +
				"WHERE " +
					"ph.date IN (SELECT MAX(ph2.date) FROM position_history ph2 GROUP BY ph2.user_iduser) and " +
					"s.id_spot=ph.spot_idspot and " +
					"u.id_user=ph.user_iduser and  ";
		
		if (usernameFilter!=null)
			queryString+="u.username like '%:usernameFilter%' and ";
		
		queryString+="" +
					"ph.user_iduser IN (SELECT ALL fu.user_iduserto FROM favorite_user fu WHERE fu.user_iduserfrom=:favoriteUserId) " +
				"GROUP BY ph.user_iduser " +
				"LIMIT :startLimit,:rowsPerPage";
		
		Query query = entityManager.createNativeQuery(queryString,PositionHistory.class);
		query.setParameter("favoriteUserId", favoriteUserId);
		query.setParameter("startLimit", pageNumber*rowsPerPage);
		query.setParameter("rowsPerPage", rowsPerPage);
		
		if (usernameFilter!=null)
			query.setParameter("usernameFilter", usernameFilter);

		return query.getResultList();
	}

	@Transactional
	public List<PositionHistory> findByNearestPositionGBuser(Integer userId, Integer pageNumber, Integer rowsPerPage, String usernameFilter, Double currentLat, Double currentLon) {
		String queryString="" +
				"SELECT ph.* " +
				"FROM position_history ph, spot s, user u " +
				"WHERE " +
					"ph.date IN (SELECT MAX(ph2.date) FROM position_history ph2 GROUP BY ph2.user_iduser) and " +
					"s.id_spot=ph.spot_idspot and " +
					"u.id_user=ph.user_iduser and ";
		
		if (usernameFilter!=null)
			queryString+="u.username like '%:usernameFilter%' and ";
		
		queryString+="" +
					"u.id_user<>:userId " +
				"GROUP BY u.id_user ";
		
		if (currentLat!=null && currentLon!=null)
			queryString+="ORDER BY distanceCoordenadas(:currentLat, ph.latitude, :currentLon, ph.longitude) desc ";
		else	
			queryString+="ORDER BY ph.date desc ";
		
		queryString+="" +
				"LIMIT :startLimit,:rowsPerPage";
		
		Query query = entityManager.createNativeQuery(queryString, PositionHistory.class);
		query.setParameter("userId", userId);
		query.setParameter("startLimit", pageNumber*rowsPerPage);
		query.setParameter("rowsPerPage", rowsPerPage);
		
		if (usernameFilter!=null)
			query.setParameter("usernameFilter", usernameFilter);
		
		if (currentLat!=null && currentLon!=null){
			DecimalFormat df = new DecimalFormat("#.#####");
			query.setParameter("currentLat", df.format(currentLat));
			query.setParameter("currentLon", df.format(currentLon));
		}
		
		return query.getResultList();
	}

}
